package org.mwielocha.jxcube.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Map;
import java.util.ResourceBundle;

public class Database {
  
  private static final String DB_FILE = "db/database.db";
  
  private static Database instance;
  
  private boolean opened;
  
  private Connection conn;
  private ResourceBundle rs;
  
  private static Database getInstance() throws ClassNotFoundException {
    if(instance == null) {
      instance = new Database();
    }
    return instance;
  }
  
  public Database() throws ClassNotFoundException {
    Class.forName("org.sqlite.JDBC");
    System.out.println("driver loaded");
    rs = ResourceBundle.getBundle("properties/msg");
  }
  
  public void open() throws SQLException {
    conn = DriverManager.getConnection("jdbc:sqlite:" + DB_FILE);
   // conn.setAutoCommit(false);
    System.out.println("connected to db");
    opened = true;
  }
  
  public void close() throws SQLException {
    if(!conn.isClosed() || !opened) {
      conn.close();
      opened = false;
    }
  }
  
  public void commit() throws SQLException {
    conn.commit();
  }
  
  public void rollback() throws SQLException {
    conn.rollback();
  }
  
  public ResultSet executeSQL(String sql) throws SQLException {
    Statement statement = conn.createStatement();
    statement.execute(sql);
    return statement.getResultSet();
  }
  
  public void executeDML(String sql) throws SQLException {
    Statement statement = conn.createStatement();
    statement.execute(sql);
  }
  
  public static String lookupQuery() {
    return getAllFields();
  }
  
  public static String lookupByFieldQuery(String fieldName, String value) {
    value = prepareString(value);
    return getAllFields() +
    		"where " + fieldName + " like '%" + value + "%'";
  }
  
  public static String lookupByAllFieldsQuery(String value) {
    value = value.replace("'", "''");
    return getAllFields() +
        getAllFieldsWhereClause(value);
  }
  
  public static String lookupAllQuery(String tableName) {
    return "select * from " + tableName;
  }
  
  public static String clear() throws SQLException {
    return clearAllQuery("songs");
  }
  
  public static String clearAllQuery(String tableName) {
    return "delete from " + tableName;
  }
  
  public static String insertQuery(String tableName, Map<String, Object> values)  {

    String fields = "";
    String objects = "";
    
    for(Map.Entry<String, Object> e : values.entrySet()) {
      fields = fields + e.getKey() + ",";
      Object o = e.getValue();
      if(o instanceof String) {
        o = "'" + prepareString(((String) o)) + "'"; 
      }
      objects = objects + o + ",";
    }
    
    String sql = "insert into " + tableName + " (" + fields.substring(0, fields.length() - 1) + ")"
      + " values (" + objects.substring(0, objects.length() - 1) + ")";
    
 //   System.out.println("SQL: " + sql);
     
    return sql;
    
  }
  
  public int count(String path) throws SQLException {
    Statement statement = conn.createStatement();
    statement.execute("select count(*) from songs where path = '" + prepareString(path) + "'");
    ResultSet rs = statement.getResultSet();
    return rs.getInt(1);
  }
  
  public static String insertQuery(Map<String, Object> values) {
    return insertQuery("songs", values);
  }
  
  public static String agregadeQuery(String fieldName){
    ResourceBundle rs = ResourceBundle.getBundle("properties/msg");
    return "select ifnull(" + fieldName + ",'" + rs.getString("general.unknown") 
    + "'), count(*) from songs group by " + fieldName;
  }
  
  public static String agregadeSearchFieldQuery(String agregadeFieldName, String searchFieldName, String value) {
    ResourceBundle rs = ResourceBundle.getBundle("properties/msg");
    value = prepareString(value);
    return "select ifnull(" + agregadeFieldName + ",'" + rs.getString("general.unknown") 
        + "'), count(*) from songs where " + searchFieldName + " like '" + value + "' group by " + agregadeFieldName;
  }
  
  public static String agregadeSearchAllFieldsQuery(String fieldName, String value) {
    ResourceBundle rs = ResourceBundle.getBundle("properties/msg");
    value = prepareString(value);
    return "select ifnull(" + fieldName + ",'" + rs.getString("general.unknown") 
        + "'), count(*) from songs " + getAllFieldsWhereClause(value) 
        + " group by " + fieldName;
  }
  
  public static String prepareString(String str) {
    return str.replace("'", "''");
  }
  
  public static String getAllFields() {
    return "select track, title, artist, album, genre, time, path from songs ";
  }
  
  public static String getAllFieldsWhereClause(String value) {
    value = value.toUpperCase();
    return " where title like upper('%" + value + "%')" +
    " OR artist like upper('%" + value + "%')" + " OR album like upper('%" + value + "%')" +
    " OR genre like upper('%" + value + "%')" + " OR path like upper('%" + value + "%')";
  }

}
